package com.freesun.monitor.db;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;


/**
 * Created by yangfei on 16-12-14.
 */
public class OperateSqlServer {

    private  Logger log = LoggerFactory.getLogger(this.getClass());
    private  String tag = "SqlServer";

    //定义连接变量
    private Connection conn;
    private Statement  st;

    /**
     * @desc 创建连接
     * @param url
     * @param username
     * @param password
     * @return
     */
    public  Statement GetConnect(String url,String username,String password) {

        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            log.debug(tag+"加载驱动:net.sourceforge.jtds.jdbc.Driver 成功!");
        } catch (ClassNotFoundException e) {
            log.error(tag+"加载驱动:net.sourceforge.jtds.jdbc.Driver 失败!");
            e.printStackTrace();
        }
        try {
            //设置连接时间10S超时
            DriverManager.setLoginTimeout(10);
            conn = DriverManager.getConnection(url,username,password);
            st = conn.createStatement();

            log.debug(tag+"建立连接"+url+"成功!");


        }catch (SQLException e) {
            log.error(tag+"建立连接"+url+"失败!");
            e.printStackTrace();
        }
        return st;
    }

    /**
     * @desc 关闭连接
     * @throws SQLException
     */
    public  void ConnectClose(){
        try {
            st.close();
            conn.close();
            log.debug(tag+"连接"+"成功!");
        } catch (SQLException e) {
            log.error(tag+"连接"+"失败!");
            e.printStackTrace();
        }

    }

    /**
     * @desc 根据查询语句返回结果集
     * @param sql
     * @return
     * @throws SQLException
     */
    public ResultSet SelectData(String sql){
        ResultSet rs = null;
        try {
            rs = st.executeQuery(sql);
            log.debug(tag+"查询"+sql+"成功!");
        } catch (SQLException e) {
            log.error(tag+"查询"+sql+"失败!");
            e.printStackTrace();
        }
        return rs;
    }


    /**
     * @desc 更新用户
     * @param sql
     * @return
     * @throws SQLException
     */
    public int UpdateData(String sql){
        int ud = 0;
        try {
            ud = st.executeUpdate(sql);
            if (ud>0){
                log.debug(tag+"更新"+sql+"成功!");
            }else {
                log.error(tag+"更新"+sql+"失败!");
            }

        } catch (SQLException e) {
            log.error(tag+"更新"+sql+"失败!");
            e.printStackTrace();
        }
        return ud;
    }


    /**
     * @desc 删除数据
     * @param sql
     * @return
     * @throws SQLException
     */
    public int DeleteData(String sql){
        int dd = 0;
        try {
            dd = st.executeUpdate(sql);
            if (dd>0){
                log.debug(tag+"删除"+sql+"成功!");
            }else {
                log.error(tag+"删除"+sql+"失败!");
            }

        } catch (SQLException e) {
            log.error(tag+"删除"+sql+"失败!");
            e.printStackTrace();
        }
        return dd;
    }


    /**
     * @desc 插入数据
     * @param sql
     * @return
     * @throws SQLException
     */
    public int InsertData(String sql){
        int id = 0;
        try {
            id = st.executeUpdate(sql);
            if (id>0){
                log.debug(tag+"插入"+sql+"成功!");
            }else {
                log.error(tag+"插入"+sql+"失败!");
            }

        } catch (SQLException e) {
            log.error(tag+"插入"+sql+"失败!");
            e.printStackTrace();
        }
        return id;
    }

}
